﻿Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data
Imports System.Collections.Generic

Public Class CategoryGroupDAO
    Public Shared _TableName As String = "tblCategoryGroup"

    Public Shared FIELDS As String = "CG.ID AS CGID, CG.CategoryGroupName AS CGCategoryGroupName"
    Public Shared Function GetCategoryGroups(ByVal pFieldNameOrderBy As String) As List(Of CategoryGroup)
        Dim strSQL As String = "SELECT  * FROM " & _TableName & " WHERE 1=1 "
        If pFieldNameOrderBy IsNot Nothing Then
            strSQL = strSQL & " ORDER BY " & pFieldNameOrderBy & ""
        End If

        Dim categoryGroups As List(Of CategoryGroup) = Nothing
        Dim m_Conn As SqlConnection = Nothing
        Dim reader As SqlDataReader = Nothing
        Try
            m_Conn = DBHelper.GetConnection()
            reader = SqlHelper.ExecuteReader(m_Conn, CommandType.Text, strSQL)
            If reader IsNot Nothing Then
                categoryGroups = New List(Of CategoryGroup)()
                While reader.Read
                    Dim strValue As New ArrayList()
                    strValue = DBHelper.ReadValue(reader)
                    Dim _rowObj As CategoryGroup = New CategoryGroup()
                    Dim indexStart As Integer = 0
                    _rowObj = GetEntity(indexStart, strValue)
                    categoryGroups.Add(_rowObj)
                End While
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            If m_Conn IsNot Nothing Then
                m_Conn.Close()
                m_Conn.Dispose()
                m_Conn = Nothing
            End If
            If reader IsNot Nothing Then
                reader.Close()
                reader = Nothing
            End If
        End Try

        Return categoryGroups
    End Function

    Protected Shared Function Insert(ByVal obj As CategoryGroup, ByVal iAutoKey As Boolean, ByRef con As SqlConnection, ByRef trans As SqlTransaction) As Boolean
        If obj Is Nothing Then
            Return False
        End If
        If iAutoKey Then
            obj.ID = System.Guid.NewGuid.ToString()
        End If
        Try
            Dim sql As String = "INSERT INTO " & _TableName & "([ID] ,[CategoryGroupName]) "
            sql &= " VALUES(@ID ,@CategoryGroupName);"

            Dim lParam As List(Of SqlParameter) = New List(Of SqlParameter)
            lParam.Add(New SqlParameter("@ID", obj.ID))
            lParam.Add(New SqlParameter("@CategoryGroupName", obj.CategoryGroupName))

            Dim lParamArray As SqlParameter() = lParam.ToArray()

            SqlHelper.ExecuteScalar(trans, CommandType.Text, sql, lParamArray)

            Return True
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
        End Try
        Return False
    End Function

    Protected Shared Function Update(ByVal obj As CategoryGroup, ByRef con As SqlConnection, ByRef trans As SqlTransaction) As Boolean
        If obj Is Nothing Then
            Return False
        End If
        Try
            Dim sql As String = ""
            sql = "UPDATE " & _TableName & " " & _
                  "SET CategoryGroupName=@CategoryGroupName " & _
                  "WHERE ID=@ID "

            Dim lParam As List(Of SqlParameter) = New List(Of SqlParameter)
            lParam.Add(New SqlParameter("@ID", obj.ID))
            lParam.Add(New SqlParameter("@CategoryGroupName", obj.CategoryGroupName))

            Dim lParamArray As SqlParameter() = lParam.ToArray()

            SqlHelper.ExecuteScalar(trans, CommandType.Text, sql, lParamArray)
            Return True
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally

        End Try

        Return False

    End Function


    Protected Shared Function ChangePassword(ByVal obj As CategoryGroup, ByRef con As SqlConnection, ByRef trans As SqlTransaction) As Boolean
        Return Update(obj, con, trans)
    End Function

    Protected Shared Function DeleteCategory(ByVal _ID As String, ByRef con As SqlConnection, ByRef trans As SqlTransaction) As Boolean
        Try
            'Dim sql As String = "UPDATE " & _TableName & " SET deleted=1 " & _
            '                    "WHERE ID=@ID"

            Dim sql As String = "DELETE FROM " & _TableName & " " & _
                                "WHERE ID=@ID "

            Dim lParamArray As SqlParameter() = Nothing
            lParamArray = New SqlParameter() { _
                New SqlParameter("@ID", _ID) _
            }
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sql, lParamArray)
            Return True
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
        End Try
        Return False
    End Function


    Public Shared Function GetEntity(ByRef indexStart As Integer, ByVal strValue As ArrayList) As CategoryGroup
        Dim obj As CategoryGroup = New CategoryGroup()
        obj.ID = strValue(indexStart)
        indexStart += 1
        obj.CategoryGroupName = strValue(indexStart)
        indexStart += 1

        Return obj
    End Function

    Public Shared Function findByID(ByVal _ID As String) As CategoryGroup
        Dim con As SqlConnection = Nothing
        Dim reader As SqlDataReader = Nothing
        Dim sql As String = "SELECT * FROM " & _TableName & " " & _
                            "WHERE ID=@ID "
        Try
            con = DBHelper.GetConnection()
            Dim lParamArray As SqlParameter() = Nothing
            lParamArray = New SqlParameter() { _
                New SqlParameter("@ID", _ID) _
            }
            reader = SqlHelper.ExecuteReader(con, CommandType.Text, sql, lParamArray)
            If reader.Read Then
                Dim strValue As New ArrayList()
                strValue = DBHelper.ReadValue(reader)
                Return GetEntity(0, strValue)
            End If
        Catch ex As Exception
            Throw New Exception(ex.Message, ex)
        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
                con = Nothing
            End If
            If reader IsNot Nothing Then
                reader.Close()
                reader = Nothing
            End If
        End Try

        Return Nothing
    End Function
End Class
